1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Threading.Tasks;
6 using System.IO;
7 using System.Data;
8 using System.Data.SqlClient;
9 using System.Windows.Forms;
10 using System.Xml;
11 namespace SoftQuanLyNhaHang.Models
12 {
13 class connection
14 {
15 //public static string sqlcon = @"server=DESKTOP-VLEK6RU\SQLExpress; uid=sa; pwd=1; database = dbSoftQuanLyNhaHang";
16 public static string sqlcon = "";
17
18 public static bool IsKet_Noi_CSDL()
19 {
20 using (var l_oConnection = SoftQuanLyNhaHang.Models.connection.Getconnection())
21 {
22 try
23 {
24 l_oConnection.Open();
25 return true;
26 }
27 catch (SqlException)
28 {
29 return false;
30 }
31 }
32 }
33
34 public static SqlConnection Getconnection()
35 {
36 string startupPath = System.IO.Directory.GetCurrentDirectory().Replace("\\bin\\Release", "\\Resources").Replace("\\bin\\Debug", "\\Resources");
37
38 XmlDocument xmlDoc = new XmlDocument();
39 xmlDoc.Load(startupPath + "/ConnectCSDL.xml");
40 XmlNodeList nodeList = xmlDoc.DocumentElement.SelectNodes("/ConnectionString");
41 foreach (XmlNode node in nodeList)
42 {
43 sqlcon = node.SelectSingleNode("SqlCon").InnerText;
44 }
45
46 sqlcon = @"" + sqlcon + "";
47
48 SqlConnection con = new SqlConnection(sqlcon);
49 return con;
50 }
51
52 public static void open()
53 {
54 try
55 {
56 if (Getconnection().State == ConnectionState.Closed)
57 Getconnection().Open();
58 }
59 catch (Exception ex)
60 {
61 MessageBox.Show("Error:" + ex.Message);
62 }
63 }
64 public static void close()
65 {
66 try
67 {
68 if (Getconnection().State == ConnectionState.Open)
69 Getconnection().Close();
70 }
71 catch (Exception ex)
72 {
73 MessageBox.Show("Error: " + ex.Message);
74 }
75 }
76
77 public static DataSet FillDataSet(string sql)
78 {
79 DataSet ds = new DataSet();
80 try
81 {
82 SqlDataAdapter da = new SqlDataAdapter(sql, Getconnection());
83 da.Fill(ds);
84 da.Dispose();
85 }
86 catch (Exception ex)
87 {
88 MessageBox.Show("Error:" + ex.Message);
89 }
90 return ds;
91 }
92 public static DataSet FillDataSet(string sql, string table)
93 {
94 DataSet ds = new DataSet();
95 try
96 {
97 SqlDataAdapter da = new SqlDataAdapter(sql, Getconnection());
98 da.Fill(ds, table);
99 da.Dispose();
100 }
101 catch (Exception ex)
102 {
103 MessageBox.Show("Error:" + ex.Message);
104 }
105 return ds;
106 }
107
108
109 public static DataSet FillDataSet(string strQuery, CommandType cmdtype)
110 {
111 DataSet ds = new DataSet();
112 try
113 {
114
115 SqlConnection con = new SqlConnection();
116 con = Getconnection();
117 con.Open();
118 SqlCommand cmd = new SqlCommand();
119 cmd.CommandText = strQuery;
120 cmd.CommandType = cmdtype;
121 cmd.Connection = con;
122
123 SqlDataAdapter da = new SqlDataAdapter(cmd);
124 da.Fill(ds);
125 da.Dispose();
126 }
127 catch (Exception ex)
128 {
129 MessageBox.Show("Error: " + ex.Message);
130 }
131 return ds;
132 }
133
134 public static DataSet FillDataSet(string strQuery, CommandType cmdtype, string[] para, object[] values)
135 {
136 DataSet ds = new DataSet();
137 try
138 {
139 SqlConnection con = new SqlConnection();
140 con = Getconnection();
141 con.Open();
142
143 SqlCommand cmd = new SqlCommand();
144 cmd.CommandText = strQuery;
145 cmd.CommandType = cmdtype;
146
147 cmd.Connection = con;
148
149 SqlParameter sqlpara;
150 for (int i = 0; i < para.Length; i++)
151 {
152 sqlpara = new SqlParameter();
153 sqlpara.ParameterName = para[i];
154 sqlpara.SqlValue = values[i];
155
156 cmd.Parameters.Add(sqlpara);
157 }
158
159 SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
160 sqlda.Fill(ds);
161 sqlda.Dispose();
162 }
163 catch (Exception ex)
164 {
165 MessageBox.Show("Error: " + ex.Message);
166 }
167 return ds;
168 }
169
170 public static DataSet DataSetReader(string strQuery, CommandType cmdtype, string[] para, object[] values)
171 {
172 DataSet dsReader = new DataSet();
173 try
174 {
175 SqlConnection con = new SqlConnection();
176 con = Getconnection();
177 con.Open();
178
179 SqlCommand cmd = new SqlCommand();
180 cmd.CommandText = strQuery;
181 cmd.CommandType = cmdtype;
182
183 cmd.Connection = con;
184
185 SqlParameter sqlpara;
186 for (int i = 0; i < para.Length; i++)
187 {
188 sqlpara = new SqlParameter();
189 sqlpara.ParameterName = para[i];
190 sqlpara.SqlValue = values[i];
191
192 cmd.Parameters.Add(sqlpara);
193 }
194 SqlDataReader dataReader;
195 dataReader = cmd.ExecuteReader();
196 dsReader.Tables[0].Load(dataReader);
197 dataReader.Close();
198
199 }
200 catch (Exception ex)
201 {
202 MessageBox.Show("Error: " + ex.Message);
203 }
204 return dsReader;
205 }
206
207 public static SqlDataReader DataReader(string strQuery, CommandType cmdtype, string[] para, object[] values)
208 {
209 SqlDataReader dataReader;
210
211 SqlConnection con = new SqlConnection();
212 con = Getconnection();
213 con.Open();
214
215 SqlCommand cmd = new SqlCommand();
216 cmd.CommandText = strQuery;
217 cmd.CommandType = cmdtype;
218
219 cmd.Connection = con;
220 SqlParameter sqlpara;
221 for (int i = 0; i < para.Length; i++)
222 {
223 sqlpara = new SqlParameter();
224 sqlpara.ParameterName = para[i];
225 sqlpara.SqlValue = values[i];
226 cmd.Parameters.Add(sqlpara);
227 }
228 dataReader = cmd.ExecuteReader();
229
230 return dataReader;
231 }
232
233 public static DataSet DataSetReader1(string strQuery, CommandType cmdtype)
234 {
235 DataSet dsReader = new DataSet();
236 try
237 {
238 SqlConnection con = new SqlConnection();
239 con = Getconnection();
240 con.Open();
241
242 SqlCommand cmd = new SqlCommand();
243 cmd.CommandText = strQuery;
244 cmd.CommandType = cmdtype;
245
246 cmd.Connection = con;
247
248 SqlDataReader dataReader;
249 dataReader = cmd.ExecuteReader();
250 dsReader.Tables[0].Load(dataReader);
251 dataReader.Close();
252
253 }
254 catch (Exception ex)
255 {
256 MessageBox.Show("Error: " + ex.Message);
257 }
258 return dsReader;
259 }
260 public static DataSet DataSetReader(string strQuery, CommandType cmdtype)
261 {
262 DataSet dsReader = new DataSet();
263 try
264 {
265 SqlConnection con = new SqlConnection();
266 con = Getconnection();
267 con.Open();
268
269 SqlCommand cmd = new SqlCommand();
270 cmd.CommandText = strQuery;
271 cmd.CommandType = cmdtype;
272
273 cmd.Connection = con;
274
275 SqlDataReader dataReader;
276 dataReader = cmd.ExecuteReader();
277 dsReader.Tables[0].Load(dataReader);
278 dataReader.Close();
279
280 }
281 catch (Exception ex)
282 {
283 MessageBox.Show("Error: " + ex.Message);
284 }
285 return dsReader;
286 }
287 public static DataTable FillDataTable(string sql)
288 {
289 DataTable dt = new DataTable();
290 try
291 {
292 SqlDataAdapter da = new SqlDataAdapter(sql, Getconnection());
293 da.Fill(dt);
294 da.Dispose();
295 }
296 catch (Exception ex)
297 {
298 MessageBox.Show("Error:" + ex.Message);
299 }
300 return dt;
301 }
302 public static int Excute_Sql(string sql)
303 {
304 int i = 0;
305 SqlConnection conn = new SqlConnection();
306 conn = Getconnection();
307 conn.Open();
308 SqlCommand cmd = new SqlCommand(sql, conn);
309 try
310 {
311 i = cmd.ExecuteNonQuery();
312 }
313 catch (Exception ex)
314 {
315 MessageBox.Show("Error: " + ex.Message);
316 }
317 return i;
318 }
319 public static int Excute_Sql(string strQuery, CommandType cmdtype, string[] para, object[] values)
320 {
321 SqlConnection conn = new SqlConnection();
322 conn = Getconnection();
323 conn.Open();
324 int efftectRecord = 0;
325 SqlCommand sqlcmd = new SqlCommand();
326 sqlcmd.CommandText = strQuery;
327 sqlcmd.Connection = conn;
328 sqlcmd.CommandType = cmdtype;
329
330 SqlParameter sqlpara;
331 for (int i = 0; i < para.Length; i++)
332 {
333 sqlpara = new SqlParameter();
334 sqlpara.ParameterName = para[i];
335 sqlpara.SqlValue = values[i];
336 sqlcmd.Parameters.Add(sqlpara);
337 }
338 try
339 {
340 efftectRecord = sqlcmd.ExecuteNonQuery();
341 }
342 catch (Exception ex)
343 {
344 MessageBox.Show("Error:" + ex.Message);
345 }
346 return efftectRecord;
347 }
348
349 public static int thucThiLenh(string sql)
350 {
351 int count = 0;
352 SqlConnection conn = new SqlConnection();
353 conn = Getconnection();
354 conn.Open();
355 {
356 SqlCommand cmd = new SqlCommand(sql, conn);
357 try
358 {
359 count = cmd.ExecuteNonQuery();
360 }
361 catch (Exception ex)
362 {
363 MessageBox.Show("Có lỗi xảy ra\r\n" + ex.Message);
364 }
365 }
366 return count;
367 }
368 public static object docGiaTri(string sql)
369 {
370 object result = null;
371 SqlConnection conn = Getconnection();
372 conn.Open();
373 SqlCommand cmd = new SqlCommand(sql, conn);
374 try
375 {
376 result = cmd.ExecuteScalar();
377 }
378 catch (Exception ex)
379 {
380 MessageBox.Show("Có lỗi xảy ra\r\n" + ex.Message);
381 }
382 return result;
383 }
384 public static string ExcuteScalar(string stringSQL)
385 {
386 string giaTri = "";
387 try
388 {
389 SqlConnection sqlconn = Getconnection();
390 sqlconn.Open();
391 SqlCommand cmd = new SqlCommand(stringSQL, sqlconn);
392 giaTri = cmd.ExecuteScalar().ToString();
393 }
394 catch { }
395 return giaTri;
396 }
397 public static string ExcuteScalar(string strQuery, CommandType cmdtype, string[] para, object[] values)
398 {
399 SqlConnection conn = new SqlConnection();
400 conn = Getconnection();
401 conn.Open();
402 string efftectRecord = "";
403 SqlCommand sqlcmd = new SqlCommand();
404 sqlcmd.CommandText = strQuery;
405 sqlcmd.Connection = conn;
406 sqlcmd.CommandType = cmdtype;
407
408 SqlParameter sqlpara;
409 for (int i = 0; i < para.Length; i++)
410 {
411 sqlpara = new SqlParameter();
412 sqlpara.ParameterName = para[i];
413 sqlpara.SqlValue = values[i];
414 sqlcmd.Parameters.Add(sqlpara);
415 }
416 try
417 {
418 efftectRecord = sqlcmd.ExecuteScalar().ToString();
419 }
420 catch (Exception ex)
421 {
422 MessageBox.Show("Error:" + ex.Message);
423 }
424 return efftectRecord;
425 }
426
427 }
428 }
